from migration.base_migrator import BaseMigrator
from utils.common import CommonUtils
import re
import os
from migration.base_migrator import BaseMigrator

class ViewMigrator(BaseMigrator):
    def __init__(self, mysql_db, kingbase_db, migration_config, logger, task_name):
        super().__init__(mysql_db, kingbase_db, migration_config, logger, task_name)
        self.views_dir = os.path.join(self.reports_dir, 'views')
        CommonUtils.ensure_directory_exists(self.views_dir)
    
    def get_views_to_process(self):
        """
        获取需要处理的视图
        """
        all_views = self.mysql_db.get_all_views()
        include_tables = self.migration_config.get('include_tables', [])
        exclude_tables = self.migration_config.get('exclude_tables', [])
        
        # 如果指定了包含表，则只处理包含的视图
        if include_tables:
            views_to_process = [view for view in all_views if view in include_tables]
        else:
            views_to_process = all_views
        
        # 排除指定的视图
        if exclude_tables:
            views_to_process = [view for view in views_to_process if view not in exclude_tables]
        
        self.logger.info(f"共需要处理 {len(views_to_process)} 个视图")
        return views_to_process
    
    def export_view_sqls(self, views):
        """
        导出视图SQL到文件，并转换为PostgreSQL/KingbaseES兼容的语法
        """
        for view_name in views:
            try:
                # 获取视图定义
                view_definition = self.mysql_db.get_view_definition(view_name)
                
                if view_definition:
                      # 转换视图SQL为PostgreSQL/KingbaseES兼容语法
                      # 使用全新的、简单直接的方法确保只生成一个CREATE VIEW语句
                        
                      # 1. 首先，移除视图定义中的所有换行符，便于处理
                      clean_sql = view_definition.replace('\n', ' ')
                        
                      # 2. 合并多余的空格
                      clean_sql = re.sub(r'\s+', ' ', clean_sql).strip()
                        
                      # 3. 使用正则表达式捕获CREATE VIEW语句后的SELECT查询部分
                      # 这是解决重复问题的关键一步
                      match = re.search(r'CREATE\s+(?:ALGORITHM=\w+\s+)?(?:DEFINER=[^\s]+\s+)?(?:SQL\s+SECURITY\s+\w+\s+)?VIEW\s+[\w.`]+\s+AS\s+(.*)', clean_sql, re.IGNORECASE | re.DOTALL)
                        
                      if match:
                          # 只保留查询部分
                          query_part = match.group(1).strip()
                            
                          # 4. 移除反引号
                          query_part = query_part.replace('`', '')
                            
                          # 5. 转换DATE_FORMAT为TO_CHAR
                          date_format_matches = list(re.finditer(r"DATE_FORMAT\s*\(\s*(\w+\.\w+)\s*,\s*'([^']+)'\s*\)", query_part, re.IGNORECASE))
                          for match in reversed(date_format_matches):
                              expr = match.group(1).strip()
                              mysql_format = match.group(2).strip()
                              # 转换日期格式模式
                              pg_format = mysql_format.replace('%Y', 'YYYY')
                              pg_format = pg_format.replace('%m', 'MM')
                              pg_format = pg_format.replace('%d', 'DD')
                              pg_format = pg_format.replace('%H', 'HH24')
                              pg_format = pg_format.replace('%i', 'MI')
                              pg_format = pg_format.replace('%s', 'SS')
                              # 替换函数调用
                              prefix = query_part[:match.start()]
                              suffix = query_part[match.end():]
                              query_part = f"{prefix}TO_CHAR({expr}, '{pg_format}'){suffix}"
                            
                          # 处理FROM和JOIN子句中的表名，包括带模式的表名和嵌套JOIN语句
                          # 先处理带模式的表名 (schema.table) - 增强版支持更多空格和嵌套情况
                          # 确保能匹配所有大小写的表名，包括括号内的表名
                          query_part = re.sub(r'(FROM|JOIN|UPDATE|INTO|ALTER TABLE|CREATE TABLE)\s+([A-Za-z0-9_]+)\.([A-Za-z0-9_]+)', lambda m: f'{m.group(1)} "{m.group(2).lower()}"."{m.group(3)}"', query_part, flags=re.IGNORECASE)
                            
                          # 再次处理括号内的表名，确保能匹配所有情况
                          query_part = re.sub(r'\(\s*([A-Za-z0-9_]+)\.([A-Za-z0-9_]+)', lambda m: f'( "{m.group(1).lower()}"."{m.group(2)}"', query_part, flags=re.IGNORECASE)
                            
                          # 再处理简单表名 - 增强版支持更多空格和嵌套情况
                          query_part = re.sub(r'(FROM|JOIN|UPDATE|INTO|ALTER TABLE|CREATE TABLE)\s+([A-Za-z0-9_]+)', lambda m: f'{m.group(1)} "{m.group(2)}"', query_part, flags=re.IGNORECASE)
                            
                          # 再次处理括号内的简单表名
                          query_part = re.sub(r'\(\s*([A-Za-z0-9_]+)', lambda m: f'( "{m.group(1)}"', query_part, flags=re.IGNORECASE)
                          
                          # 处理ON子句中的完整条件表达式，确保等号两边的字段名都正确添加双引号
                          # 首先移除重复的括号
                          query_part = re.sub(r'ON\s*\(\(', 'ON (', query_part, flags=re.IGNORECASE)
                          query_part = re.sub(r'\)\)', ')', query_part, flags=re.IGNORECASE)
                          
                          # 处理带模式的表名和字段名比较 (schema.table.field = schema.table.field)
                          # 增加捕获括号外的情况，确保能匹配各种格式的ON子句
                          query_part = re.sub(r'ON\s*\(\s*"?([A-Za-z0-9_]+)"?\."?([A-Za-z0-9_]+)"?\."?([A-Za-z0-9_]+)"?\s*=\s*"?([A-Za-z0-9_]+)"?\."?([A-Za-z0-9_]+)"?\."?([A-Za-z0-9_]+)"?\s*\)', 
                                             lambda m: f'ON ( "{m.group(1).lower()}"."{m.group(2)}"."{m.group(3)}" = "{m.group(4).lower()}"."{m.group(5)}"."{m.group(6)}")', 
                                             query_part, flags=re.IGNORECASE)
                          # 处理带表名的字段名比较 (table.field = table.field)
                          query_part = re.sub(r'ON\s*\(\s*"?([A-Za-z0-9_]+)"?\."?([A-Za-z0-9_]+)"?\s*=\s*"?([A-Za-z0-9_]+)"?\."?([A-Za-z0-9_]+)"?\s*\)', 
                                             lambda m: f'ON ( "{m.group(1).lower()}"."{m.group(2)}" = "{m.group(3).lower()}"."{m.group(4)}")', 
                                             query_part, flags=re.IGNORECASE)
                          # 处理单边带引号的情况 - 增强版，处理更多情况
                          query_part = re.sub(r'ON\s*\(\s*"([A-Za-z0-9_]+)"\."([A-Za-z0-9_]+)"\.([A-Za-z0-9_]+)\s*=\s*(.+?)\s*\)', 
                                             lambda m: f'ON ( "{m.group(1).lower()}"."{m.group(2)}"."{m.group(3)}" = {m.group(4)})', 
                                             query_part, flags=re.IGNORECASE)
                          # 处理未加引号的字段名 - 增强版
                          query_part = re.sub(r'ON\s*\(\s*("?[A-Za-z0-9_]+"?\."?[A-Za-z0-9_]+"?\.("?[A-Za-z0-9_]+"?))\s*=\s*([A-Za-z0-9_]+)\.([A-Za-z0-9_]+)\.([A-Za-z0-9_]+)', 
                                             lambda m: f'ON ( {m.group(1)} = "{m.group(3).lower()}"."{m.group(4)}"."{m.group(5)}")', 
                                             query_part, flags=re.IGNORECASE)
                          query_part = re.sub(r'ON\s*\(\s*("?[A-Za-z0-9_]+"?\.("?[A-Za-z0-9_]+"?))\s*=\s*([A-Za-z0-9_]+)\.([A-Za-z0-9_]+)', 
                                             lambda m: f'ON ( {m.group(1)} = "{m.group(3).lower()}"."{m.group(4)}")', 
                                             query_part, flags=re.IGNORECASE)
                            
                          # 处理SELECT语句中的字段名，为每个字段名添加双引号
                          # 查找SELECT子句和FROM子句之间的部分
                          select_match = re.search(r'SELECT\s+(.*?)(?:\s+FROM|\s+WHERE|\s+GROUP\s+BY|\s+HAVING|\s+ORDER\s+BY|$)', query_part, re.IGNORECASE | re.DOTALL)
                          if select_match:
                              select_list = select_match.group(1).strip()
                              fields = []
                              # 处理逗号分隔的字段列表
                              for field in select_list.split(','):
                                  field = field.strip()
                                  # 跳过包含函数调用或表达式的字段
                                  if any(op in field.upper() for op in ['(', '=', '>', '<', '!=', '<>', '+', '-', '*', '/', '%', '||']):
                                      fields.append(field)
                                  else:
                                      # 处理带别名的字段 (field AS alias)
                                      as_pos = field.upper().find(' AS ')
                                      if as_pos > 0:
                                          field_name = field[:as_pos].strip()
                                          alias = field[as_pos + 4:].strip()
                                          # 为字段名和别名添加双引号
                                          if '.' in field_name:
                                              # 处理带模式和表名的字段 (schema.table.field)
                                              parts = field_name.split('.')
                                              if len(parts) == 3:
                                                  fields.append(f'"{parts[0].lower()}"."{parts[1]}"."{parts[2]}" AS "{alias}"')
                                              else:
                                                  fields.append(f'"{parts[0].lower()}"."{parts[1]}" AS "{alias}"')
                                          else:
                                              fields.append(f'"{field_name}" AS "{alias}"')
                                      else:
                                          # 处理简单字段名添加双引号
                                          if '.' in field:
                                              # 处理带模式和表名的字段 (schema.table.field)
                                              parts = field.split('.')
                                              if len(parts) == 3:
                                                  fields.append(f'"{parts[0].lower()}"."{parts[1]}"."{parts[2]}"')
                                              else:
                                                  fields.append(f'"{parts[0].lower()}"."{parts[1]}"')
                                          else:
                                              fields.append(f'"{field}"')
                                
                              # 重建SELECT子句
                              new_select_list = ', '.join(fields)
                              prefix = query_part[:select_match.start(1)]
                              suffix = query_part[select_match.end(1):]
                              query_part = f'{prefix}{new_select_list}{suffix}'
                                
                              # 6. 移除可能的分号结尾
                              query_part = query_part.rstrip(';')
                                
                              # 7. 最终构建只包含一个CREATE VIEW语句的SQL
                              # 使用小写的模式名，与kingbase_db.py保持一致
                              target_schema_lower = self.target_schema.lower()
                              # 为视图名也添加双引号
                              final_view_sql = f"CREATE VIEW {target_schema_lower}.\"{view_name}\" AS {query_part}";
                          else:
                              # 如果没有找到CREATE VIEW语句，使用原始定义
                              # 这是一个安全保障
                              # 使用小写的模式名，与kingbase_db.py保持一致
                              target_schema_lower = self.target_schema.lower()
                              final_view_sql = f"CREATE VIEW {target_schema_lower}.{view_name} AS {clean_sql}";
                                
                          # 保存到文件
                          view_file = os.path.join(self.views_dir, f"{view_name}_view.sql")
                          CommonUtils.write_text_file(view_file, final_view_sql)
                          self.logger.info(f"视图SQL导出成功: {view_file}")
            except Exception as e:
                self.logger.error(f"视图SQL导出失败: {view_name}\n{e}")
    
    def create_views_in_database(self, views):
        """
        在数据库中创建视图
        """
        for view_name in views:
            try:
                # 获取视图定义
                view_definition = self.mysql_db.get_view_definition(view_name)
                
                if view_definition:
                    # 转换视图SQL为PostgreSQL/KingbaseES兼容语法
                    final_view_sql = self.convert_view_sql(view_definition, view_name)
                    
                    # 在数据库中创建视图
                    self.kingbase_db.execute_update(final_view_sql)
                    self.logger.info(f"视图在数据库中创建成功: {view_name}")
            except Exception as e:
                self.logger.error(f"视图在数据库中创建失败: {view_name}\n{e}")
    
    def convert_view_sql(self, mysql_view_sql, view_name):
        """
        将MySQL视图SQL转换为PostgreSQL/KingbaseES兼容的视图SQL
        """
        # 1. 预处理SQL语句，确保正确处理换行符和多余空格
        processed_sql = mysql_view_sql.replace('\n', ' ')
        processed_sql = re.sub(r'\s+', ' ', processed_sql).strip()
        
        # 2. 提取最核心的SELECT查询部分
        # 寻找AS关键字，它标志着视图定义中SELECT查询的开始
        as_pos = processed_sql.lower().find(' as ')
        if as_pos > 0:
            # 提取AS关键字后面的所有内容
            query_content = processed_sql[as_pos + 4:].strip()
        else:
            # 如果没有找到AS关键字，尝试直接查找SELECT关键字
            select_pos = processed_sql.lower().find('select')
            if select_pos >= 0:
                query_content = processed_sql[select_pos:].strip()
            else:
                # 如果都找不到，使用整个SQL
                query_content = processed_sql
        
        # 3. 彻底清理查询内容，移除任何可能存在的CREATE VIEW子句
        query_content = re.sub(r'create\s+view\s+[\w`\.]+\s+as\s+', '', query_content, flags=re.IGNORECASE)
        
        # 4. 确保查询内容以SELECT开始
        if not query_content.lower().startswith('select'):
            select_pos = query_content.lower().find('select')
            if select_pos >= 0:
                query_content = query_content[select_pos:].strip()
        
        # 5. 移除表名和列名中的反引号
        query_content = query_content.replace('`', '')
        
        # 6. 处理FROM和JOIN子句中的表名，包括带模式的表名和嵌套JOIN语句
        # 先处理带模式的表名 (schema.table) - 增强版支持更多空格和嵌套情况
        # 确保能匹配所有大小写的表名，包括括号内的表名
        query_content = re.sub(r'(FROM|JOIN|UPDATE|INTO|ALTER TABLE|CREATE TABLE)\s+([A-Za-z0-9_]+)\.([A-Za-z0-9_]+)', lambda m: f'{m.group(1)} "{m.group(2).lower()}"."{m.group(3)}"', query_content, flags=re.IGNORECASE)
        
        # 再次处理括号内的表名，确保能匹配所有情况
        query_content = re.sub(r'\(\s*([A-Za-z0-9_]+)\.([A-Za-z0-9_]+)', lambda m: f'( "{m.group(1).lower()}"."{m.group(2)}"', query_content, flags=re.IGNORECASE)
        
        # 再处理简单表名 - 增强版支持更多空格和嵌套情况
        # 将所有表名转换为小写后再添加双引号，确保与PostgreSQL默认行为一致
        query_content = re.sub(r'(FROM|JOIN|UPDATE|INTO|ALTER TABLE|CREATE TABLE)\s+([A-Za-z0-9_]+)', lambda m: f'{m.group(1)} "{m.group(2).lower()}"', query_content, flags=re.IGNORECASE)
        
        # 再次处理括号内的简单表名，同样将表名转换为小写
        query_content = re.sub(r'\(\s*([A-Za-z0-9_]+)', lambda m: f'( "{m.group(1).lower()}"', query_content, flags=re.IGNORECASE)
        
        # 7. 处理ON子句中的完整条件表达式，确保等号两边的字段名都正确添加双引号
        # 首先移除重复的括号
        query_content = re.sub(r'ON\s*\(\(', 'ON (', query_content, flags=re.IGNORECASE)
        query_content = re.sub(r'\)\)', ')', query_content, flags=re.IGNORECASE)
        
        # 处理带模式的表名和字段名比较 (schema.table.field = schema.table.field)
        # 增加捕获括号外的情况，确保能匹配各种格式的ON子句
        query_content = re.sub(r'ON\s*\(\s*"?([A-Za-z0-9_]+)"?\."?([A-Za-z0-9_]+)"?\."?([A-Za-z0-9_]+)"?\s*=\s*"?([A-Za-z0-9_]+)"?\."?([A-Za-z0-9_]+)"?\."?([A-Za-z0-9_]+)"?\s*\)', 
                                lambda m: f'ON ( "{m.group(1).lower()}"."{m.group(2)}"."{m.group(3)}" = "{m.group(4).lower()}"."{m.group(5)}"."{m.group(6)}")', 
                                query_content, flags=re.IGNORECASE)
        # 处理带表名的字段名比较 (table.field = table.field)
        query_content = re.sub(r'ON\s*\(\s*"?([A-Za-z0-9_]+)"?\."?([A-Za-z0-9_]+)"?\s*=\s*"?([A-Za-z0-9_]+)"?\."?([A-Za-z0-9_]+)"?\s*\)', 
                                lambda m: f'ON ( "{m.group(1).lower()}"."{m.group(2)}" = "{m.group(3).lower()}"."{m.group(4)}")', 
                                query_content, flags=re.IGNORECASE)
        # 处理单边带引号的情况 - 增强版，处理更多情况
        query_content = re.sub(r'ON\s*\(\s*"([A-Za-z0-9_]+)"\."([A-Za-z0-9_]+)"\.([A-Za-z0-9_]+)\s*=\s*(.+?)\s*\)', 
                                lambda m: f'ON ( "{m.group(1).lower()}"."{m.group(2)}"."{m.group(3)}" = {m.group(4)})', 
                                query_content, flags=re.IGNORECASE)
        # 处理未加引号的字段名 - 增强版
        query_content = re.sub(r'ON\s*\(\s*("?[A-Za-z0-9_]+"?\."?[A-Za-z0-9_]+"?\.("?[A-Za-z0-9_]+"?))\s*=\s*([A-Za-z0-9_]+)\.([A-Za-z0-9_]+)\.([A-Za-z0-9_]+)', 
                                lambda m: f'ON ( {m.group(1)} = "{m.group(3).lower()}"."{m.group(4)}"."{m.group(5)}")', 
                                query_content, flags=re.IGNORECASE)
        query_content = re.sub(r'ON\s*\(\s*("?[A-Za-z0-9_]+"?\.("?[A-Za-z0-9_]+"?))\s*=\s*([A-Za-z0-9_]+)\.([A-Za-z0-9_]+)', 
                                lambda m: f'ON ( {m.group(1)} = "{m.group(3).lower()}"."{m.group(4)}")', 
                                query_content, flags=re.IGNORECASE)
        
        # 特殊处理ON子句中复杂条件表达式的表名字段引用，确保所有表名都正确引用
        # 查找ON子句中的所有表名.字段名格式并添加双引号
        on_clauses = list(re.finditer(r'ON\s*\((.*?)\)', query_content, re.IGNORECASE | re.DOTALL))
        for on_match in reversed(on_clauses):
            on_content = on_match.group(1)
            # 查找所有表名.字段名格式
            field_refs = list(re.finditer(r'([A-Za-z0-9_]+)\.([A-Za-z0-9_]+)', on_content))
            for field_ref in reversed(field_refs):
                table_name = field_ref.group(1)
                column_name = field_ref.group(2)
                # 检查是否已经添加了引号
                if not field_ref.group(0).startswith('"'):
                    # 保持表名的原始大小写并添加引号
                    new_ref = f'"{table_name.lower()}"."{column_name}"' 
                    # 替换原始字符串中的匹配部分
                    before_ref = query_content[:on_match.start(1) + field_ref.start()]
                    after_ref = query_content[on_match.start(1) + field_ref.end():]
                    query_content = f'{before_ref}{new_ref}{after_ref}'
        
        # 8. 处理SELECT语句中的字段名，为每个字段名添加双引号
        # 查找SELECT子句和FROM子句之间的部分
        select_match = re.search(r'SELECT\s+(.*?)(?:\s+FROM|\s+WHERE|\s+GROUP\s+BY|\s+HAVING|\s+ORDER\s+BY|$)', query_content, re.IGNORECASE | re.DOTALL)
        if select_match:
            select_list = select_match.group(1).strip()
            fields = []
            # 处理逗号分隔的字段列表
            for field in select_list.split(','):
                field = field.strip()
                # 跳过包含函数调用或表达式的字段
                if any(op in field.upper() for op in ['(', '=', '>', '<', '!=', '<>', '+', '-', '*', '/', '%', '||']):
                    fields.append(field)
                else:
                    # 处理带别名的字段 (field AS alias)
                    as_pos = field.upper().find(' AS ')
                    if as_pos > 0:
                        field_name = field[:as_pos].strip()
                        alias = field[as_pos + 4:].strip()
                        # 为字段名和别名添加双引号
                        if '.' in field_name:
                            # 处理带模式和表名的字段 (schema.table.field)
                            parts = field_name.split('.')
                            if len(parts) == 3:
                                fields.append(f'"{parts[0].lower()}"."{parts[1]}"."{parts[2]}" AS "{alias}"')
                            else:
                                fields.append(f'"{parts[0].lower()}"."{parts[1]}" AS "{alias}"')
                        else:
                            fields.append(f'"{field_name}" AS "{alias}"')
                    else:
                        # 处理简单字段名添加双引号
                        if '.' in field:
                            # 处理带模式和表名的字段 (schema.table.field)
                            parts = field.split('.')
                            if len(parts) == 3:
                                fields.append(f'"{parts[0].lower()}"."{parts[1]}"."{parts[2]}"')
                            else:
                                fields.append(f'"{parts[0].lower()}"."{parts[1]}"')
                        else:
                            fields.append(f'"{field}"')
            
            # 重建SELECT子句
            new_select_list = ', '.join(fields)
            prefix = query_content[:select_match.start(1)]
            suffix = query_content[select_match.end(1):]
            query_content = f'{prefix}{new_select_list}{suffix}'
        
        # 9. 处理MySQL特有的函数和语法
        # 转换DATE_FORMAT为TO_CHAR
        date_format_patterns = list(re.finditer(r'DATE_FORMAT\s*\(\s*([^,]+?),\s*[\'"]?([^,\'"\s]+?)[\'"]?\s*\)', query_content, re.IGNORECASE))
        for match in reversed(date_format_patterns):
            expr = match.group(1).strip()
            mysql_format = match.group(2).strip()
            # 转换日期格式模式
            pg_format = mysql_format.replace('%Y', 'YYYY')
            pg_format = pg_format.replace('%m', 'MM')
            pg_format = pg_format.replace('%d', 'DD')
            pg_format = pg_format.replace('%H', 'HH24')
            pg_format = pg_format.replace('%i', 'MI')
            pg_format = pg_format.replace('%s', 'SS')
            # 替换函数调用
            prefix = query_content[:match.start()]
            suffix = query_content[match.end():]
            query_content = f"{prefix}TO_CHAR({expr}, '{pg_format}'){suffix}"
        
        # 转换IFNULL为COALESCE
        query_content = query_content.replace('IFNULL(', 'COALESCE(')
        
        # 10.1 再次检查并修复FROM子句中的表名，确保都已正确引用并转换为小写
        # 处理带别名的表名 (table AS alias)
        query_content = re.sub(r'(FROM|JOIN)\s+([A-Za-z0-9_]+)\s+AS\s+([A-Za-z0-9_]+)', 
                               lambda m: f'{m.group(1)} "{m.group(2).lower()}" AS {m.group(3)}', 
                               query_content, flags=re.IGNORECASE)
        
        # 处理不带AS的表名别名 (table alias)
        query_content = re.sub(r'(FROM|JOIN)\s+([A-Za-z0-9_]+)\s+([A-Za-z0-9_]+)', 
                               lambda m: f'{m.group(1)} "{m.group(2).lower()}" {m.group(3)}', 
                               query_content, flags=re.IGNORECASE)
        
        # 再次检查所有FROM子句中的表名，确保没有遗漏
        # 查找所有FROM子句中的表名
        from_clauses = list(re.finditer(r'(FROM|JOIN)\s+([A-Za-z0-9_]+)', query_content, re.IGNORECASE))
        for from_match in reversed(from_clauses):
            keyword = from_match.group(1)
            table_name = from_match.group(2)
            # 检查是否已经添加了引号
            if not from_match.group(0).endswith('"'):
                # 将表名转换为小写并添加引号
                new_from = f'{keyword} "{table_name.lower()}"' 
                # 替换原始字符串中的匹配部分
                before_from = query_content[:from_match.start()]
                after_from = query_content[from_match.end():]
                query_content = f'{before_from}{new_from}{after_from}'
        
        # 10. 清理查询内容，确保语法正确
        # 移除多余的分号
        query_content = query_content.rstrip(';')
        
        # 添加WHERE条件特殊处理
        # 检查WHERE子句是否正确闭合
        where_pos = query_content.lower().find(' where ')
        if where_pos > 0:
            # 提取WHERE子句内容
            where_content = query_content[where_pos + 7:].strip()
            # 检查WHERE子句中的括号是否匹配
            where_left_brackets = where_content.count('(')
            where_right_brackets = where_content.count(')')
            if where_left_brackets > where_right_brackets:
                # 为WHERE子句补充缺失的右括号
                query_content += ')' * (where_left_brackets - where_right_brackets)
            
            # 处理WHERE子句中的字段名，确保大小写与数据库中一致
            # 1. 查找所有表名.字段名格式并保留原始大小写
            where_content = query_content[where_pos + 7:].strip()
            # 匹配形如 table.field 或 "table".field 或 "table"."field" 的模式
            field_patterns = list(re.finditer(r'("?[A-Za-z0-9_]+"?)\.("?[A-Za-z0-9_]+"?)', where_content))
            for match in reversed(field_patterns):
                table_part = match.group(1)
                field_part = match.group(2)
                # 如果字段名没有引号，保持其原始大小写并添加引号
                if not field_part.startswith('"') and not field_part.endswith('"'):
                    # 保留字段名的原始大小写
                    new_field = f'{table_part}."{field_part}"' 
                    # 替换原始字符串中的匹配部分
                    before_match = query_content[:match.start() + where_pos + 7]
                    after_match = query_content[match.end() + where_pos + 7:]
                    query_content = f'{before_match}{new_field}{after_match}'
        
        # 加强版括号匹配检查和修复
        # 1. 首先检查整体括号匹配
        left_brackets = query_content.count('(')
        right_brackets = query_content.count(')')
        
        # 2. 补充缺失的右括号
        if left_brackets > right_brackets:
            query_content += ')' * (left_brackets - right_brackets)
        elif right_brackets > left_brackets:
            # 如果右括号过多，在开头补充左括号
            query_content = '(' * (right_brackets - left_brackets) + query_content
        
        # 3. 检查SQL语句结构完整性
        # 确保WHERE子句后面没有悬空的AND/OR
        query_content = re.sub(r'(WHERE\s+.*?)(\s+(AND|OR)\s*)$', r'\1', query_content, flags=re.IGNORECASE | re.DOTALL)
        
        # 4. 确保逻辑操作符后面有条件
        # 查找单独的AND或OR在语句末尾
        query_content = re.sub(r'(\s+(AND|OR)\s*)$', '', query_content, flags=re.IGNORECASE)
        
        # 11. 构建完整的PostgreSQL视图SQL，只生成一个CREATE VIEW语句
        # 使用小写的模式名并为视图名添加双引号
        target_schema_lower = self.target_schema.lower()
        final_view_sql = f"CREATE VIEW {target_schema_lower}.\"{view_name}\" AS {query_content};"
        
        return final_view_sql